/********************************************
 * 功能说明: 
 * 模块名称: 
 * 系统名称: 
 * 软件版权: 
 * 系统版本: 1.0.0
 * 开发人员: zhangfb
 * 开发时间: 2019/4/3 16:26
 * 审核人员: 
 * 相关文档: 
 * 修改记录: 修改日期 修改人员 修改说明
 *********************************************/
package com.hyacinth.support;

import com.hyacinth.config.DBType;
import com.hyacinth.domain.PageInfo;
import com.hyacinth.utils.Assert;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.AbstractListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;

/**
 * 分页函数
 * @author zhangfb
 * @version 1.0.0.1
 * @since JDK 1.8
 */
public class Pagination {
    private static final Logger LOGGER = LoggerFactory.getLogger(Pagination.class);

    private final int pageSize;        // 每页条数
    private long currPage;       // 当前页码
    private long totalRows = 0;      // 总条数
    private long totalPages = 0;     // 总页数
    private long startIndex = 0;     // 起始行数
    private long lastIndex = 0;      // 结束行数

    private final DBType dbType;
    private final QueryRunner queryRunner; // 查询器

    private final String querySql;  // 查询语句
    private final ResultSetHandler handler; // 数据结果Handler

    private String countSql = null;  // 计数sql语句
    private boolean optimizeCountSql = true; // 是否优化 (sql中有多个from或多个order by时置为false)

    private List<Map> resultList = null;
    private Object[] params = null; // SQL 绑定参数

    public Pagination(DBType dbType,
                      QueryRunner queryRunner,
                      ResultSetHandler handler,
                      String querySql,
                      int pageSize,
                      long currPage) {
        this.dbType = dbType;
        this.queryRunner = queryRunner;
        this.querySql = querySql;
        this.handler = handler;
        this.currPage = currPage;
        this.pageSize = pageSize;
    }

    /**
     * 生成count sql
     * @param querySql 查询语句
     * @return 统计SQL
     */
    public String genCountSql(String querySql) {
        StringJoiner joiner = new StringJoiner(querySql);
        joiner.add("select count(1) from (").add(") a");
        return joiner.toString();
    }

    /**
     * 生产快速的统计语句
     * @param querySql 查询语句
     * @return 统计SQL
     */
    public String genCountFastSql(String querySql) {
        String sql = querySql;
        int groupPos = sql.lastIndexOf("group by");
        if (groupPos > 0) {
            return genCountSql(sql);
        }
        int orderPos = sql.lastIndexOf("order by");
        int fromPos = sql.indexOf("from");
        if (orderPos > 0) {
            sql = sql.substring(fromPos, orderPos);
        } else {
            sql = sql.substring(fromPos);
        }
        return "select count(1) " + sql;
    }

    private String getTotalSQL() {
        return countSql == null ? (isOptimizeCountSql() ? genCountFastSql(querySql) : genCountSql(querySql)) : countSql;
    }

    public PageInfo queryData(String pkColumnName) throws Exception {
        Assert.notNull(queryRunner, "queryRunner is required, please initial it first");
        Assert.notNull(querySql, "querySql is required !!!");
        // 计算总条数
        setTotalRows();
        // 计算总页数
        setTotalPages();
        // 计算当前页
        setCurrPage();
        // 计算起始行数
        setStartIndex();
        // 计算结束行数
        setLastIndex();

        // 构建分页sql
        String pageSql = buildPageSql(pkColumnName);
        LOGGER.debug("==> Preparing: {}",pageSql);
        LOGGER.debug("==> Parameters: {}",params);
        // 执行查询
        executeQuery(pageSql);
        LOGGER.debug("<== result: {}",resultList);
        // 查询结果集
        return new PageInfo(totalPages, currPage, totalRows, resultList.size(), pageSize, resultList);
    }

    private void executeQuery(String pageSql) throws SQLException {
        Assert.notNull(handler, "resultSet handler required!!!");
        if (this.params == null) {
            this.resultList = (List<Map>) queryRunner.query(pageSql, handler);
        } else {
            this.resultList = (List<Map>) queryRunner.query(pageSql, handler,  params);
        }
    }

    private String buildPageSql(String pkColumnName) {
        StringJoiner joiner = new StringJoiner(querySql);
        switch (this.dbType) {
            case MYSQL:
            case DAMENG:
                // 构造oracle数据库的分页语句
                // 构造mysql数据库的分页语句
                joiner.add("SELECT * from ( ");
                joiner.add(" limit " + startIndex + "," + pageSize + ") z");
                break;
            case ORACLE:
                // 构造oracle数据库的分页语句
                joiner.add("SELECT * FROM (SELECT temp.* ,ROWNUM num FROM ( ");
                joiner.add("　) temp where ROWNUM <= " + lastIndex + ") WHERE　num > "+ startIndex);
                break;
            case SQLSERVER:
                long totalTopCount = pageSize * ((currPage - 1) - 1) + pageSize;
                joiner.add("SELECT * FROM (");
                joiner.add(") Z ORDER BY Z." + pkColumnName + " DESC OFFSET " + pageSize * (currPage - 1) + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY");
                break;
            default:
                break;
        }
        return joiner.toString();
    }


    private void setTotalRows() throws SQLException {
        // 查询计数sql语句
        String totalSQL = getTotalSQL();
        LOGGER.debug("==> Preparing: "+totalSQL);
        // 查询总条数
        Long count = null;
        if (this.totalRows <= 0) {
            Number countObj = null;
            if (params == null) {
                countObj = queryRunner.query(totalSQL, new ScalarHandler<>());
            } else {
                countObj = queryRunner.query(totalSQL, new ScalarHandler<>(), params);
            }
            //oracle 查询count返回的是BigDecimal数据类型，Mysql查询count返回的是BigDecimal数据类型是Long
            if (countObj instanceof BigDecimal) {
                BigDecimal bigDecimalCount = (BigDecimal) countObj;
                count = bigDecimalCount.longValue();
            } else if (countObj instanceof Long) {
                count = (Long) countObj;
            } else if (countObj instanceof Integer) {
                count = countObj.longValue();
            }
            this.totalRows = (count == null ? 0 : count);
        }
        LOGGER.debug("<==    Total: " + this.totalRows);
    }

    private void setLastIndex() {
        if (currPage < totalPages) {
            lastIndex = currPage * pageSize;
        } else if (currPage == totalPages) {
            lastIndex = totalRows;
        } else if (totalRows < pageSize) {
            lastIndex = totalRows;
        }
    }

    private void setStartIndex() {
        this.startIndex = (currPage - 1) * pageSize;
    }

    private boolean isOptimizeCountSql() {
        return optimizeCountSql;
    }

    public void setOptimizeCountSql(boolean optimizeCountSql) {
        this.optimizeCountSql = optimizeCountSql;
    }


    private void setTotalPages() {
        if (totalRows % pageSize == 0) {
            totalPages = totalRows / pageSize;
        } else {
            totalPages = totalRows / pageSize + 1;
        }
    }

    private void setCurrPage() {
        if (currPage > totalPages) {
            currPage = totalPages;
        }
        if (currPage < 1) {
            currPage = 1;
        }
    }

    public static void main(String[] args) throws Exception {
        DBOption processor = new DBOptionProxy(new DBOptionProcessor(DBType.MYSQL, "127.0.0.1", 3306, "root", "6012328", "test", 10)).getProxy();

        QueryRunner runner = new QueryRunner(processor.getHyds().getDataSource());
        Pagination p = new Pagination(DBType.MYSQL, runner, new MapListHandler(), "select * from user", 3, 2);
        System.out.println(p.queryData("id"));
    }
}
